/***
    Amrita ITEWS - Copyright (C) 2009 Amrita Vishwa Vidyapeetham, Amritapuri.
                                       (http://www.amrita.edu)
    ***************************************************************************
    This file is part of the Amrita ITEWS distribution.
    Amrita ITEWS is free software; you can redistribute it and/or modify it 
    under the terms of the GNU General Public License (version 2) as published
    by the Free Software Foundation AND MODIFIED BY the Amrita ITEWS exception.
    ***NOTE*** The exception to the GPL is included to allow you to distribute
    a combined work that includes Amrita ITEWS without being obliged to provide
    the source code for proprietary components outside of the Amrita ITEWS
    software. Amrita ITEWS is distributed in the hope that it will be useful, 
    but WITHOUT ANY WARRANTY; without even the implied warranty of 
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
    Public License for more details. You should have received a copy of the GNU
    General Public License and the Amrita ITEWS license exception along with 
    Amrita ITEWS if not then it can be viewed here: 
    http://itews.amrita.ac.in/license.html.

    Documentation, latest information, license and contact details are at:
    http://itews.amrita.ac.in/

    Amrita ITEWS source code can be found at:
    http://code.google.com/p/itews

    The initial code-base of Amrita ITEWS was developed by Amrita Vishwa 
    Vidyapeetham as part of the project titled,"Intelligent & Interactive 
    Telematics using Emerging Wireless Technologies for Transport Systems 
    (ITEWS)" sponsored by Technology Information Forecasting and Assessment 
    Council (TIFAC), India.
***/

package tdcsinfoservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.StringTokenizer;
import org.knopflerfish.service.log.LogRef;
import org.osgi.service.event.Event;

public class RouteScheduleInfoDatabaseUpdater
{
	private static final String CONTROL_PARAM = "control_param";
	private static final String DELETE_OPT = "delete";
	private static final String NODE_SCHED_LIST = "node_sched_list";
	private static final String POI_CODE_LIST = "poi_code_list";
	private static final String ROUTE_SEGMENT = "route_segment";
	private static final String ROUTE_SCHED_CODE = "route_sched_code";
	private static final String ROUTE_SCHED_DESC = "route_sched_desc";
	private static final String NODE_CODE = "node_code";
	private static final String NODE_ARR_TOD = "node_arr_tod";
	private static final String NODE_ARR_DOW = "node_arr_dow";
	private static final String NODE_DEP_TOD = "node_dep_tod";
	private static final String NODE_DEP_DOW = "node_dep_dow";
	private DBConnectionPool dbConnectionPool;
	private TDCSInfoServiceConfig config;
	private  LogRef log;
	private String routeSchedulesQuery = "SELECT route_schedule_desc FROM route_schedules WHERE route_schedule_code=?";
	private String routeSchedulesFullInsert = "INSERT INTO route_schedules VALUES(default, ?, ?, 0, 'F', 'F')";
	private String routeSchedulesUpdate = "UPDATE route_schedules SET route_schedule_desc=? WHERE route_schedule_code=?";
	private String routeSchedulesDelete = "DELETE FROM route_schedules WHERE route_schedule_code=?";
	private String routeScheduleNodeAssocInsert = "INSERT INTO route_schedule_node_assoc VALUES(default,?,?,?,?,?,?,?)";
	private String routeScheduleNodeAssocDelete = "DELETE FROM route_schedule_node_assoc WHERE route_schedule_code=?";
	private String nodesQuery = "SELECT id from nodes where node_code=?";
	private String nodesInsertPartial = "INSERT INTO nodes VALUES(default, ?, 999, 999, GeomFromText('Polygon((0 0))'))";
	private String pointsOfInterestQuery = "SELECT id from points_of_interest where code=?";
	private String pointsOfInterestInsert = "INSERT INTO points_of_interest VALUES(default, ?, ?, ?)";
	private String pointsOfInterestEnrouteDelete = "DELETE FROM points_of_interest_enroute WHERE route_schedule_code=?";
	private String pointsOfInterestEnrouteInsert = "INSERT INTO points_of_interest_enroute VALUES(default, ?,?)";
	private String routeSegmentsDelete = "DELETE FROM route_segments WHERE route_schedule_code=?";
	private String routeSegmentsInsert = "INSERT INTO route_segments VALUES(default,?,?,?,?,GeomFromText(?))";

	public RouteScheduleInfoDatabaseUpdater()
	{
		try
		{
			log = new LogRef(Activator.bc);
			config = TDCSInfoServiceConfig.getConfig();
			dbConnectionPool = DBConnectionPool.getInstance(config.getJdbcConnectionURI());
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public synchronized void updateRouteScheduleDatabase(Event event)
	{
		Connection conn = null;
		PreparedStatement preparedStmt1 = null;
		PreparedStatement preparedStmt2 = null;
		PreparedStatement preparedStmt2A = null;
		PreparedStatement preparedStmt3 = null;
		PreparedStatement preparedStmt3A = null;
		PreparedStatement preparedStmt4 = null;
		PreparedStatement preparedStmt4A = null;
		PreparedStatement preparedStmt5 = null;
		PreparedStatement preparedStmt5A = null;
		PreparedStatement preparedStmt5B = null;
		PreparedStatement preparedStmt6 = null;
		PreparedStatement preparedStmt7 = null;
		PreparedStatement preparedStmt8 = null;
		PreparedStatement preparedStmt9 = null;
		ResultSet rs1 = null;
		ResultSet rs4 = null;
		ResultSet rs6 = null;
		try 
		{
			Object routeSchedCodeObj = event.getProperty(ROUTE_SCHED_CODE);
			Object routeSchedDescObj =  event.getProperty(ROUTE_SCHED_DESC );
			Object controlParmObj = event.getProperty(CONTROL_PARAM);
			Object routeSegmentDetailsObj = event.getProperty(ROUTE_SEGMENT);
			Object nodeSchedListObj = event.getProperty(NODE_SCHED_LIST);
			Object poiListObj = event.getProperty(POI_CODE_LIST);
			if (routeSchedCodeObj == null || ((String)routeSchedCodeObj).trim().equals("") )
			{
				throw new Exception("Route Schedule Info database update aborted. Route Schedule Code blank or not available in feed");
			}
			if (controlParmObj == null && (routeSchedDescObj == null || ((String)routeSchedDescObj).trim().equals("")))
			{
				throw new Exception("Route Schedule Info database update aborted. Route Schedule Description blank or not available in feed");
			}
			if (controlParmObj == null && (nodeSchedListObj == null || ((ArrayList) nodeSchedListObj).size() == 0))
			{
				throw new Exception("Route Schedule Info database update aborted. Node Schedule List not available.");
			}
			if (controlParmObj == null && (routeSegmentDetailsObj == null || ((String)routeSegmentDetailsObj).trim().equals("")))
			{
				throw new Exception("Route Schedule Info database update aborted. Route Segment info not available.");
			}
			String routeSchedCodeStr = (String) routeSchedCodeObj;
			String routeSchedDescStr =  (String) routeSchedDescObj;
			String routeSegmentDetails = (String) routeSegmentDetailsObj;
			ArrayList nodeSchedList = (ArrayList) nodeSchedListObj;
			conn = dbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			
			// Handle route schedule details			
			
			if (controlParmObj == null) //if route schedule is to be inserted or updated
			{
				preparedStmt1 = dbConnectionPool.getPreparedStmt(conn, routeSchedulesQuery);
				preparedStmt1.setString(1, routeSchedCodeStr);
				rs1 = preparedStmt1.executeQuery();
				if(rs1.next() == false)
				{
					preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, routeSchedulesFullInsert);
					preparedStmt2.setString(1, routeSchedCodeStr);
					preparedStmt2.setString(2, routeSchedDescStr);
					preparedStmt2.executeUpdate();
					preparedStmt2.close();
					preparedStmt2 = null;
				}
				else
				{
					preparedStmt2A = dbConnectionPool.getPreparedStmt(conn, routeSchedulesUpdate);
					preparedStmt2A.setString(1, routeSchedDescStr);
					preparedStmt2A.setString(2, routeSchedCodeStr);
					preparedStmt2A.executeUpdate();
					preparedStmt2A.close();
					preparedStmt2A = null;
				}
				rs1.close();
				rs1 = null;
				preparedStmt1.close();
				preparedStmt1 = null;
				
				//Handle route segment details
				preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, routeSegmentsDelete);
				preparedStmt3.setString(1, routeSchedCodeStr);
				preparedStmt3.executeUpdate();
				preparedStmt3.close();
				preparedStmt3 = null;
				
				StringTokenizer st = new StringTokenizer(routeSegmentDetails, ";");
				if (st.hasMoreTokens())
				{
					String currentLongLat = null;
					String nextLongLat = null;
					currentLongLat = st.nextToken();
					StringTokenizer st1 = new StringTokenizer(currentLongLat, " ");
					double currentLong  = Double.parseDouble(st1.nextToken());
					double currentLat =  Double.parseDouble(st1.nextToken());
					int seqNo = 0;
					preparedStmt3A = dbConnectionPool.getPreparedStmt(conn, routeSegmentsInsert);
					preparedStmt3A.setString(1, routeSchedCodeStr);
					preparedStmt3A.setDouble(2, currentLong);
					preparedStmt3A.setDouble(3, currentLat);
					preparedStmt3A.setInt(4, seqNo);
					preparedStmt3A.setString(5, "LINESTRING(" + currentLongLat + "," +currentLongLat + ")");
					preparedStmt3A.executeUpdate();
					while(st.hasMoreTokens())
					{
						++seqNo;
						nextLongLat = st.nextToken();
						st1 = new StringTokenizer(nextLongLat, " ");
						double nextLong = Double.parseDouble(st1.nextToken());
						double nextLat=  Double.parseDouble(st1.nextToken());
						preparedStmt3A.setString(1, routeSchedCodeStr);
						preparedStmt3A.setDouble(2, nextLong);
						preparedStmt3A.setDouble(3, nextLat);
						preparedStmt3A.setInt(4, seqNo);
						preparedStmt3A.setString(5, "LINESTRING(" + currentLongLat + "," +nextLongLat + ")");
						preparedStmt3A.executeUpdate();
						currentLongLat = nextLongLat;
					}
					preparedStmt3A.close();
					preparedStmt3A = null;
				}
				else
				{
					throw new Exception("Invalid Route segment details in feed for route code: "+routeSchedCodeStr);
				}
				//Handle poi list details
				/*
				 * POI is NOT mandatory. If included, update the relevant information
				 */
				if (poiListObj != null && ((ArrayList)poiListObj).size() != 0)
				{
					preparedStmt5 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestEnrouteDelete);
					preparedStmt5.setString(1, routeSchedCodeStr);
					preparedStmt5.executeUpdate();
					preparedStmt5.close();
					preparedStmt5 = null;
					
					ArrayList poiList = (ArrayList) poiListObj;
					for (int i=0; i<poiList.size(); i++)
					{
						String poiCode = (String) poiList.get(i);
						preparedStmt4 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestQuery);
						preparedStmt4.setString(1, poiCode);
						rs4 = preparedStmt4.executeQuery();
						if (rs4.next() == false)
						{
							preparedStmt4A = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestInsert);
							preparedStmt4A.setString(1, poiCode);
							preparedStmt4A.setDouble(2, 999);
							preparedStmt4A.setDouble(3, 999);
							preparedStmt4A.executeUpdate();
							preparedStmt4A.close();
							preparedStmt4A = null;
							System.out.println("{POIs not yet available while handling routeinfo feed. Did a partial insert in route_schedules table.  Poi Code: "+poiCode);							
						}
						rs4.close();
						rs4 = null;
						preparedStmt4.close();
						preparedStmt4 = null;

						preparedStmt5A = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestEnrouteInsert);
						preparedStmt5A.setString(1, routeSchedCodeStr);
						preparedStmt5A.setString(2, poiCode);
						preparedStmt5A.executeUpdate();
						preparedStmt5A.close();
						preparedStmt5A = null;
					}
				}
				
				//Handle node list details
				
				preparedStmt5B = dbConnectionPool.getPreparedStmt(conn, routeScheduleNodeAssocDelete);
				preparedStmt5B.setString(1, routeSchedCodeStr);
				preparedStmt5B.executeUpdate();
				preparedStmt5B.close();
				preparedStmt5B = null;

				int nodeSeqno = 0;
				for (int i=0; i<nodeSchedList.size(); i++)
				{
					String nodeCode=null, nodeArrTod=null, nodeDepTod=null;
					int nodeArrDow=0, nodeDepDow=0;
					Object attr=null;
					HashMap nodeMember = (HashMap) nodeSchedList.get(i);
					attr = nodeMember.get(NODE_CODE);
					if (attr != null) nodeCode = (String) attr;
					attr = nodeMember.get(NODE_ARR_TOD);
					if (attr != null) nodeArrTod = (String) attr;
					attr = nodeMember.get(NODE_ARR_DOW);
					if (attr != null) nodeArrDow = (Integer) attr;
					attr = nodeMember.get(NODE_DEP_TOD);
					if (attr != null) nodeDepTod = (String) attr;
					attr = nodeMember.get(NODE_DEP_DOW);
					if (attr != null) nodeDepDow = (Integer) attr;
					if (nodeCode!=null && nodeArrTod!=null && nodeArrDow!=0 && nodeDepTod!=null && nodeDepDow!=0)
					{
						preparedStmt6 = dbConnectionPool.getPreparedStmt(conn, nodesQuery);
						preparedStmt6.setString(1, nodeCode);
						rs6 = preparedStmt6.executeQuery();
						if (rs6.next() == false) //if nodes table entry is to be inserted
						{
							preparedStmt7 = dbConnectionPool.getPreparedStmt(conn, nodesInsertPartial);
							preparedStmt7.setString(1, nodeCode);
							preparedStmt7.executeUpdate();
							preparedStmt7.close();
							preparedStmt7 = null;
						}
						rs6.close();
						rs6 = null;
						preparedStmt6.close();
						preparedStmt6 = null;
						
						preparedStmt8 = dbConnectionPool.getPreparedStmt(conn, routeScheduleNodeAssocInsert);
						preparedStmt8.setString(1, routeSchedCodeStr);
						preparedStmt8.setString(2, nodeCode);
						preparedStmt8.setTime(3, Time.valueOf(nodeArrTod));
						preparedStmt8.setInt(4, nodeArrDow);
						preparedStmt8.setTime(5, Time.valueOf(nodeDepTod));
						preparedStmt8.setInt(6, nodeDepDow);
						preparedStmt8.setInt(7, nodeSeqno);
						preparedStmt8.executeUpdate();
						preparedStmt8.close();
						preparedStmt8 = null;
					}
					else
					{
						throw new Exception("Invalid Node Schedule List details in feed for route code: "+routeSchedCodeStr);
					}
					nodeSeqno++;
				} // end of 'for (int i=0; i<nodeSchedList.size(); i++)'
			}
			else if (controlParmObj != null) // if route schedule is to be deleted
			{
				/*
				 * Tables dependent on route_schedules table -trigger_descriptors, route_segments, 
				 * route_schedule_node_assoc, points_of_interest_enroute
				 * are all cascade deleted because of foreign key constraints
				 */
				String controlParmStr = (String) controlParmObj;
				if (controlParmStr.trim().equals(DELETE_OPT))
				{
					preparedStmt9 = dbConnectionPool.getPreparedStmt(conn, routeSchedulesDelete);
					preparedStmt9.setString(1, routeSchedCodeStr);
					preparedStmt9.executeUpdate();
					preparedStmt9.close(); 
					preparedStmt9 = null;
				}
			} // end of 'if route schedule is to be inserted, updated or deleted' clause
			conn.commit();
		}
 		catch (Exception e) 
 		{
 			System.out.println("Failed to update routeInfo in Database: "+ e);
 			e.printStackTrace();
 		}
 		finally
 		{
 	   		if (rs1 != null)
    		{
    			try {rs1.close();} catch (SQLException e) {} 
    			rs1 = null;
    		}
 			if (rs4 != null)
    		{
    			try {rs4.close();} catch (SQLException e) {} 
    			rs4 = null;
    		}
 			if (rs6 != null)
    		{
    			try {rs6.close();} catch (SQLException e) {} 
    			rs6 = null;
    		}
    		if (preparedStmt1 != null)
    		{
    			try {preparedStmt1.close();} catch (SQLException e) {} 
    			preparedStmt1 = null;
    		}
    		if (preparedStmt2 != null)
    		{
    			try {preparedStmt2.close();} catch (SQLException e) {} 
    			preparedStmt2 = null;
    		}
    		if (preparedStmt2A != null)
    		{
    			try {preparedStmt2A.close();} catch (SQLException e) {} 
    			preparedStmt2A = null;
    		}
    		if (preparedStmt3 != null)
    		{
    			try {preparedStmt3.close();} catch (SQLException e) {} 
    			preparedStmt3 = null;
    		}
    		if (preparedStmt3A != null)
    		{
    			try {preparedStmt3A.close();} catch (SQLException e) {} 
    			preparedStmt3A = null;
    		}
    		if (preparedStmt4 != null)
    		{
    			try {preparedStmt4.close();} catch (SQLException e) {} 
    			preparedStmt4 = null;
    		}
    		if (preparedStmt4A != null)
    		{
    			try {preparedStmt4A.close();} catch (SQLException e) {} 
    			preparedStmt4A = null;
    		}
    		if (preparedStmt5 != null)
    		{
    			try {preparedStmt5.close();} catch (SQLException e) {} 
    			preparedStmt5 = null;
    		}
    		if (preparedStmt5A != null)
    		{
    			try {preparedStmt5A.close();} catch (SQLException e) {} 
    			preparedStmt5A = null;
    		}
    		if (preparedStmt5B != null)
    		{
    			try {preparedStmt5B.close();} catch (SQLException e) {} 
    			preparedStmt5B = null;
    		}
    		if (preparedStmt6 != null)
    		{
    			try {preparedStmt6.close();} catch (SQLException e) {} 
    			preparedStmt6 = null;
    		}
    		if (preparedStmt7 != null)
    		{
    			try {preparedStmt7.close();} catch (SQLException e) {} 
    			preparedStmt7 = null;
    		}
    		if (preparedStmt8 != null)
    		{
    			try {preparedStmt8.close();} catch (SQLException e) {} 
    			preparedStmt8 = null;
    		}
    		if (preparedStmt9 != null)
    		{
    			try {preparedStmt9.close();} catch (SQLException e) {} 
    			preparedStmt9 = null;
    		}
			if (conn != null)
			{
				try {conn.close();} catch (SQLException e) {}
				conn = null;
			}
 		}
	}
}
